<?php

class multitender_model_billing extends multitender_model {

    function  __construct() {
        parent::__construct();
        $this->db      = & $this->conf['dbs']['person'];
        if (is_null($this->db)) {
            trigger_error('Where DB person?');
            exit;
        }
    }

    function get_rates() {
        $sql="SELECT * FROM rates";
        $rate = $this->db->GetAll($sql);
        return $rate;
    }

    function get_rate($id) {
        $id = (int) $id;

        $sql="SELECT * FROM rates WHERE id=? LIMIT 1";
        $rate = $this->db->GetRow($sql, array($id));
        return $rate;
    }

    function get_kind_rates() {
        $sql="SELECT * FROM kind_rates";
        $kind_rate = $this->db->GetAll($sql);
        return $kind_rate;
    }

    function get_kind_rate($id) {
        $id = (int) $id;

        $sql="SELECT * FROM kind_rates WHERE id=? LIMIT 1";
        $kind_rate = $this->db->GetRow($sql, array($id));
        return $kind_rate;
    }


    function add_payment($user_id, $kind_rates_id, $rates_id, $count, $summa, $type_payment) {
        // Добавка платежа.
        //  $user_id - пользователь
        //  $kind_rates_id - вид тарифа
        //  $rates_id - тариф (период)
        //  $count - количество (тендеров/срок в месяцах)
        //  $summa - сумма в рублях

        $date = $this->db->DBTimeStamp(time());
        $sql="INSERT INTO payments(user_id, datetime, kind_rates_id, rates_id, count, summa, type_payment) " .
                "VALUES(?,$date,?,?,?,?,?)";
        $arr=array($user_id, $kind_rates_id, $rates_id, $count, $summa, $type_payment);

        if ($this->db->Execute($sql, $arr)) {
            return $this->db->Insert_ID();
        } else {
            return false;
        }
    }

    function get_payments_no($user_id, $offset=0, $count=100) {
        $offset = (int) $offset;
        $count = (int) $count;

        $sql="SELECT * FROM payments WHERE user_id=? AND paid = 0 AND datetime > '" . date('Y-m-d h:i:s', time()-60*60*24*30) . "' ORDER BY id DESC LIMIT ?, ?";

        if ($pays=$this->db->GetAll($sql, array($user_id, $offset, $count))) {
            return $pays;
        } else {
            return false;
        }
    }

    function get_payments_yes($user_id, $offset = 0, $count = 100) {
        $offset = (int) $offset;
        $count = (int) $count;

        $sql="SELECT SQL_CALC_FOUND_ROWS * FROM payments WHERE user_id=? AND paid > 0 ORDER BY id DESC LIMIT ?, ?";
        if ($pays=$this->db->GetAll($sql, array($user_id, $offset, $count))) {
            $pays['total'] = $this->db->GetOne("SELECT FOUND_ROWS()");
            return $pays;
        } else {
            return false;
        }
    }

    function get_payments($user_id, $offset=0, $count=100) {
        $offset = (int) $offset;
        $count = (int) $count;

        $sql="SELECT * FROM payments WHERE user_id=? ORDER BY id DESC LIMIT ?, ?";

        if ($pays=$this->db->GetAll($sql, array($user_id, $offset, $count))) {
            return $pays;
        } else {
            return false;
        }
    }

    function get_payments_all($offset=0, $count=0) {
        $offset = (int) $offset;
        $count = (int) $count;

        $sql="SELECT * FROM payments ORDER BY id DESC";
        if ($count > 0) {
            $sql.=" LIMIT $offset, $count";
        }

        if ($pays=$this->db->GetAll($sql)) {
            return $pays;
        } else {
            return false;
        }
    }

    function get_payments_filter($filter=null, $offset=0, $count=10) {
        $offset = (int) $offset;
        $count = (int) $count;

        $payments = array();

        if (!empty($filter['inn'])) {
            $sql = "SELECT * FROM firms WHERE INN LIKE '".$filter['inn']."%'";
            $firms = $this->db->GetAll($sql);
            foreach ($firms as $f) {
                $user_ids[] = $f['user_id'];
            }

            if (empty($user_ids)) {
                return false;
                exit;
            }
        }

        $sql ="SELECT SQL_CALC_FOUND_ROWS * FROM payments ";

        $amp="WHERE";

        if (!empty($filter['pay_id'])) {
            $sql.=$amp." id=".$filter['pay_id']." ";
            $amp ="AND";
        }

        if (!empty($user_ids)) {
            $sql.=$amp." user_id IN (".implode($user_ids,",").") ";
        }

        $sql.="ORDER BY id DESC ";
        if ($count>0) {
            $sql.="LIMIT $offset, $count";
        }

        if ($pays = $this->db->GetAll($sql)) {
            $count = $this->db->Execute("SELECT FOUND_ROWS()");
            if (empty($firms)) {
                foreach ($pays as $p) { $user_ids[] = $p['user_id']; }
                $sql = "SELECT * FROM firms WHERE user_id IN (".implode($user_ids,",").")";
                $firms = $this->db->GetAll($sql);
            }
            foreach ($pays as &$p) {
                foreach ($firms as $f) {
                    if ($p['user_id']==$f['user_id']) {
                        $p['firms'] = $f;
                    }
                }
            }
            $pays['count'] = $count;
            return $pays;
        } else {
            return false;
        }
    }

    function set_status_payments($user_id, $payable) {
        if (!empty($payable[-1])) {
            $sql="UPDATE payments SET paid = -1 WHERE user_id=? AND id IN (".implode($payable[-1],',').")";
            $this->db->Execute($sql, array($user_id));
        }
        if (!empty($payable[0])) {
            $sql="UPDATE payments SET paid = 0 WHERE user_id=? AND id IN (".implode($payable[0],',').")";
            $this->db->Execute($sql, array($user_id));
        }
    }

    function set_status_payment($user_id, $id, $status) {
        if (!$user_id && !$id) {
            exit;
        }
        $sql="UPDATE payments SET paid = -1 WHERE user_id=? AND id=?";
        $this->db->Execute($sql, array($user_id, $id));
    }

    function get_payment($payment_id) {
        $payment_id = (int) $payment_id;

        $sql="SELECT * FROM payments WHERE id=? LIMIT 1";

        if ($payment=$this->db->GetRow($sql, array($payment_id))) {
            return $payment;
        } else {
            return false;
        }
    }

    function setAccess_ins($user_id) {
        // FIXME ставить вчерашнюю дату

        if (!$user_idn=$this->db->GetOne("SELECT user_id FROM access WHERE user_id=?", array($user_id))) {
            $sql="INSERT INTO access(user_id, todate, tenders, count_puchased) VALUES(?,'2010-02-08',?,?)";
            if ($this->db->Execute($sql,array($user_id, 0, 0))) {
                return $user_id;
            } else {
                return false;
            }
        } else {
            if ($user_idn) {
                return $user_idn;
            } else {
                return false;
            }
        }
    }

    function updateAccess($user_id, $date) {
        $user_id=$this->setAccess_ins($user_id);
        if ($user_id) {
            $sql="UPDATE access SET todate='$date' WHERE user_id=$user_id";
            $this->db->Execute($sql);
        }
    }

    function setAccess(array $payment) {
        // Из предположения что в Access уже всегда существует пользователь (при выставлении счёта он туда попадает)
        //if (!$user_id=$this->setAccess_ins($payment['user_id'])) {
        //    return false;
        //    exit;
        //};

        // VESNA до 31 мая
        if (time() < strtotime('2010-06-01')) {
            $payment['count'] = $payment['count'] + 1;
        }

        $user_id = $payment['user_id'];

        $long_month = 60 * 60 * 24 * 31;

        if ($payment['kind_rates_id']==1) {
            $access = $this->getAccess($user_id);
            $todate = $access['todate'];
            if (strtotime($todate)<time()) {
                $todate = date("Y-m-d", time()             + $long_month*$payment['count']);
            } else {
                $todate = date("Y-m-d", strtotime($todate) + $long_month*$payment['count']);
            }
            $sql="UPDATE access SET todate='$todate' WHERE user_id=$user_id";
        } else {
            $sql="UPDATE access SET tenders=tenders+".$payment['count'].", count_puchased=count_puchased+".$payment['count']." WHERE user_id=$user_id";
        }

        if ($this->db->Execute($sql)) {
            return true;
        } else {
            return false;
        }
    }

    function getAccess($user_id) {
        // Возвращает состояния доступа к тендеру, пользователя $user_id
        $sql = "SELECT * FROM access WHERE user_id=? LIMIT 1";
        $acc = $this->db->GetRow($sql, array($user_id));
        if ($acc) {
            return $acc;
        } else {
            return false;
        }
    }

    function view_usertender($user_id, $item_id) {
        $user_id = (int) $user_id;
        $item_id = (int) $item_id;

        $date = $this->db->DBTimeStamp(time());
        $sql="INSERT INTO users_tenders(user_id, item_id, date_last_viewing)
                VALUES(?, ?, $date)
                ON DUPLICATE KEY UPDATE date_last_viewing=$date";
        if ($this->db->Execute($sql, array($user_id, $item_id))) {
            return true;
        } else {
            return false;
        }
    }

    function get_usertender($user_id, $item_id) {
        $user_id = (int) $user_id;
        $item_id = (int) $item_id;

        $sql = "SELECT * FROM users_tenders WHERE user_id=? AND item_id=?";
        $pur = $this->db->GetRow($sql, array($user_id, $item_id));
        if ($pur) {
            return $pur;
        } else {
            return false;
        }
    }

    function buy_usertender($user_id, $item_id, $kind_rates_id) {
        $date = $this->db->DBTimeStamp(time());
        switch ($kind_rates_id) {
            case 1:
                $sql ="UPDATE users_tenders SET date_purches=$date, kind_rates_id=?
                       WHERE item_id=$item_id AND user_id=$user_id";
                return $this->db->Execute($sql, array($kind_rates_id));
                break;
            case 2:
                try {
                    $access = $this->getAccess($user_id);
                    $tenders = $access['tenders'];
                    if ($tenders <= 0) {
                        return false;
                        exit;
                    }

                    $this->db->Execute("START TRANSACTION");
                    $sql ="UPDATE users_tenders SET date_purches=$date, kind_rates_id=?
                           WHERE item_id=$item_id AND user_id=$user_id";
                    $this->db->Execute($sql, array($kind_rates_id));
                    $this->db->Execute("UPDATE access SET tenders=".($tenders-1)." WHERE user_id=$user_id");
                    $this->db->Execute("COMMIT");
                    return true;
                } catch(exception $e) {
                    $this->db->Execute("ROLLBACK");
                    return false;
                }
                break;
        }
    }

    function get_tenders($user_id, $offset=0, $count=100, $kind="last") {
        $user_id = (int) $user_id;
        $offset  = (int) $offset; if ($offset < 0) {$offset=0;}
        $count   = (int) $count;

        $sql="SELECT * FROM users_tenders WHERE user_id=?";
        if ($kind=="purc") {
            $sql.=" AND date_purches IS NOT NULL";
        }
        $sql.=" ORDER BY date_last_viewing DESC LIMIT ?, ?";

        if ($tenders = $this->db->GetAll($sql, array($user_id, $offset, $count))) {
            return $tenders;
        } else {
            return false;
        }
    }

    function get_counttenders($user_id, $kind="last") {
        $user_id = (int) $user_id;

        $sql="SELECT count(*) FROM users_tenders WHERE user_id=?";
        if ($kind=="purc") {
            $sql.=" AND date_purches IS NOT NULL";
        }

        if ($tenders = $this->db->GetOne($sql, array($user_id))) {
            return $tenders;
        } else {
            return false;
        }
    }

    function get_balance($user_id) {
        $user_id = (int) $user_id;

        $sql="SELECT balance FROM money WHERE user_id=? ORDER BY id DESC";
        if ($bal=$this->db->GetOne($sql, array($user_id))) {
            return $bal;
        } else {
            return 0;
        }
    }

    function paid($payments_id) {
        $payment=$this->get_payment($payments_id);
        if (!$payment) {
            return false;
        }

        // Платеж проходит два раза
        // ($payment['paid'] == 1)
        // Очень плохое место для проверки, уникальность транзакции можно гарантировать только внутри нее.

        $user_id = $payment['user_id'];

        try {
            $db_date = $this->db->DBTimeStamp(time());
            $this->db->Execute("START TRANSACTION");
            $exist = $this->db->GetOne("SELECT paid FROM payments WHERE id=?", $payment['id']);
            if ((int)$exist !== 0) {
                $this->db->Execute("ROLLBACK");
                return false;                
            }
            $balance = $this->get_balance($user_id)+$payment['summa']*100;
            $this->db->Execute("INSERT INTO money(user_id, income, datetime, balance, payments_id) VALUES(?, ?, $db_date, ?, ?)",
                    array($user_id, $payment['summa']*100, $balance, $payments_id));
            $balance = $balance-$payment['summa']*100;
            $this->setAccess($payment);
            $this->db->Execute("UPDATE payments SET paid=1, datetime_adopted=$db_date WHERE id=?", array($payments_id));
            $this->db->Execute("INSERT INTO money(user_id, income, datetime, balance, payments_id) VALUES(?, ?, $db_date, ?, ?)",
                    array($user_id, -$payment['summa']*100, $balance, $payments_id));
            $this->db->Execute("UPDATE access SET money_balance=? WHERE user_id=?", array($balance, $user_id));
            $this->db->Execute("COMMIT");
            return true;
        } catch(exception $e) {
            $this->db->Execute("ROLLBACK");
            return false;
        }
    }

}
